Data Cleaning

library(tidyverse)
── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.1.4     ✔ readr     2.1.5
✔ forcats   1.0.0     ✔ stringr   1.5.1
✔ ggplot2   3.5.2     ✔ tibble    3.3.0
✔ lubridate 1.9.4     ✔ tidyr     1.3.1
✔ purrr     1.1.0     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
library(dplyr)
library(readr)

Import the raw dataset

df <- read_csv("military.csv")
Rows: 174 Columns: 77
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (77): Country, 1949, 1950, 1951, 1952, 1953, 1954, 1955, 1956, 1957, 195...

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

Inspect the dataset

glimpse(df)
Rows: 174
Columns: 77
$ Country <chr> "Algeria", "Libya", "Morocco", "Tunisia", "Angola", "Benin", "…
$ `1949`  <chr> "...", "...", "...", "...", "...", "...", "...", "...", "...",…
$ `1950`  <chr> "...", "...", "...", "...", "...", "...", "...", "...", "...",…
$ `1951`  <chr> "...", "...", "...", "...", "...", "...", "...", "...", "...",…
$ `1952`  <chr> "...", "...", "...", "...", "...", "...", "...", "...", "...",…
$ `1953`  <chr> "...", "...", "...", "...", "...", "...", "...", "...", "...",…
$ `1954`  <chr> "...", "...", "...", "...", "...", "...", "...", "...", "...",…
$ `1955`  <chr> "...", "...", "...", "...", "...", "...", "...", "...", "...",…
$ `1956`  <chr> "...", "...", "1.18%", "...", "...", "...", "...", "...", "...…
$ `1957`  <chr> "...", "...", "1.76%", "...", "...", "...", "...", "...", "...…
$ `1958`  <chr> "...", "...", "2.14%", "...", "...", "...", "...", "...", "...…
$ `1959`  <chr> "...", "...", "2.47%", "...", "...", "...", "...", "...", "...…
$ `1960`  <chr> "1.61%", "1.59%", "2.32%", "2.00%", "...", "0.87%", "...", "0.…
$ `1961`  <chr> "1.92%", "1.86%", "2.70%", "2.22%", "...", "1.40%", "...", "0.…
$ `1962`  <chr> "...", "3.10%", "2.57%", "1.73%", "...", "1.75%", "...", "2.35…
$ `1963`  <chr> "2.51%", "2.41%", "3.20%", "1.51%", "...", "1.74%", "...", "2.…
$ `1964`  <chr> "3.50%", "1.84%", "2.84%", "1.74%", "...", "2.18%", "...", "2.…
$ `1965`  <chr> "3.42%", "1.86%", "2.43%", "1.29%", "...", "2.11%", "...", "1.…
$ `1966`  <chr> "3.51%", "2.91%", "2.68%", "1.32%", "...", "1.90%", "...", "1.…
$ `1967`  <chr> "3.02%", "3.36%", "2.62%", "1.42%", "...", "1.98%", "...", "1.…
$ `1968`  <chr> "2.61%", "2.86%", "3.00%", "1.44%", "...", "2.07%", "...", "1.…
$ `1969`  <chr> "2.39%", "3.76%", "2.39%", "1.31%", "...", "1.89%", "...", "1.…
$ `1970`  <chr> "2.13%", "6.07%", "2.32%", "1.56%", "...", "1.63%", "...", "1.…
$ `1971`  <chr> "2.09%", "5.32%", "2.63%", "1.48%", "...", "1.60%", "...", "1.…
$ `1972`  <chr> "1.80%", "3.71%", "2.84%", "1.41%", "...", "1.49%", "...", "1.…
$ `1973`  <chr> "1.69%", "3.98%", "3.06%", "1.40%", "...", "1.68%", "...", "1.…
$ `1974`  <chr> "1.96%", "3.29%", "3.15%", "1.31%", "...", "1.44%", "...", "1.…
$ `1975`  <chr> "2.13%", "2.25%", "4.60%", "1.74%", "...", "1.49%", "...", "2.…
$ `1976`  <chr> "2.70%", "1.97%", "6.22%", "1.86%", "...", "1.28%", "...", "3.…
$ `1977`  <chr> "2.24%", "2.46%", "6.62%", "2.38%", "...", "1.68%", "1.36%", "…
$ `1978`  <chr> "2.38%", "3.80%", "5.84%", "2.49%", "...", "1.19%", "2.87%", "…
$ `1979`  <chr> "2.14%", "3.22%", "5.63%", "2.24%", "...", "1.93%", "3.50%", "…
$ `1980`  <chr> "2.10%", "2.92%", "5.94%", "2.03%", "...", "2.03%", "3.21%", "…
$ `1981`  <chr> "1.82%", "1.76%", "6.38%", "2.48%", "...", "1.55%", "2.98%", "…
$ `1982`  <chr> "2.06%", "2.24%", "6.22%", "3.69%", "...", "1.05%", "2.69%", "…
$ `1983`  <chr> "1.95%", "...", "4.51%", "4.17%", "...", "1.19%", "2.33%", "2.…
$ `1984`  <chr> "1.76%", "...", "4.02%", "2.76%", "...", "1.46%", "2.44%", "3.…
$ `1985`  <chr> "1.64%", "...", "5.39%", "2.34%", "0.00%", "1.30%", "2.18%", "…
$ `1986`  <chr> "1.79%", "...", "4.30%", "2.09%", "0.00%", "1.36%", "2.66%", "…
$ `1987`  <chr> "1.86%", "...", "4.37%", "1.82%", "22.25%", "1.57%", "4.79%", …
$ `1988`  <chr> "1.75%", "...", "4.13%", "2.10%", "21.98%", "1.56%", "4.90%", …
$ `1989`  <chr> "1.54%", "...", "4.39%", "2.11%", "19.43%", "1.31%", "3.57%", …
$ `1990`  <chr> "1.46%", "...", "4.15%", "1.84%", "17.46%", "1.23%", "4.45%", …
$ `1991`  <chr> "1.21%", "...", "4.14%", "1.83%", "7.10%", "...", "4.60%", "2.…
$ `1992`  <chr> "2.14%", "...", "4.30%", "1.71%", "5.68%", "...", "4.48%", "2.…
$ `1993`  <chr> "2.51%", "...", "4.66%", "1.73%", "17.52%", "...", "4.93%", "1…
$ `1994`  <chr> "3.15%", "...", "4.49%", "1.74%", "5.71%", "...", "4.01%", "1.…
$ `1995`  <chr> "2.94%", "...", "4.35%", "1.74%", "4.71%", "...", "3.51%", "1.…
$ `1996`  <chr> "3.09%", "...", "3.95%", "1.87%", "2.45%", "...", "2.90%", "1.…
$ `1997`  <chr> "3.64%", "4.08%", "4.19%", "1.73%", "5.97%", "...", "3.20%", "…
$ `1998`  <chr> "3.97%", "5.30%", "3.46%", "1.68%", "2.64%", "...", "3.78%", "…
$ `1999`  <chr> "3.76%", "3.78%", "2.42%", "1.56%", "17.33%", "0.49%", "3.09%"…
$ `2000`  <chr> "3.43%", "3.15%", "2.69%", "1.55%", "6.39%", "0.41%", "3.19%",…
$ `2001`  <chr> "3.52%", "2.41%", "3.73%", "1.52%", "4.52%", "0.36%", "3.83%",…
$ `2002`  <chr> "3.41%", "2.07%", "3.49%", "1.49%", "2.87%", "0.62%", "4.11%",…
$ `2003`  <chr> "3.00%", "2.07%", "3.49%", "1.48%", "3.76%", "0.65%", "4.01%",…
$ `2004`  <chr> "3.05%", "2.07%", "3.25%", "1.43%", "3.47%", "0.68%", "3.51%",…
$ `2005`  <chr> "2.73%", "1.53%", "3.26%", "1.45%", "3.69%", "0.68%", "2.86%",…
$ `2006`  <chr> "2.51%", "1.12%", "3.11%", "1.45%", "3.76%", "0.67%", "2.75%",…
$ `2007`  <chr> "2.77%", "0.95%", "3.05%", "1.26%", "3.11%", "...", "2.90%", "…
$ `2008`  <chr> "2.87%", "1.49%", "3.18%", "1.29%", "3.57%", "0.66%", "3.10%",…
$ `2009`  <chr> "3.51%", "...", "3.29%", "1.29%", "4.71%", "...", "3.26%", "1.…
$ `2010`  <chr> "3.19%", "...", "3.39%", "1.24%", "4.18%", "...", "2.76%", "1.…
$ `2011`  <chr> "3.96%", "...", "3.30%", "1.49%", "3.26%", "...", "2.40%", "1.…
$ `2012`  <chr> "4.11%", "3.75%", "3.46%", "1.44%", "3.24%", "0.70%", "2.34%",…
$ `2013`  <chr> "4.42%", "7.64%", "3.81%", "1.56%", "4.46%", "0.69%", "2.15%",…
$ `2014`  <chr> "4.96%", "15.48%", "3.40%", "1.81%", "4.70%", "0.70%", "2.21%"…
$ `2015`  <chr> "5.55%", "...", "2.96%", "2.14%", "3.11%", "0.80%", "2.83%", "…
$ `2016`  <chr> "5.65%", "...", "2.98%", "2.22%", "2.73%", "0.67%", "3.41%", "…
$ `2017`  <chr> "5.31%", "...", "2.95%", "2.03%", "2.51%", "0.92%", "3.25%", "…
$ `2018`  <chr> "4.93%", "...", "2.90%", "1.98%", "1.96%", "0.63%", "2.92%", "…
$ `2019`  <chr> "5.33%", "...", "2.87%", "2.38%", "1.77%", "0.47%", "3.08%", "…
$ `2020`  <chr> "5.89%", "...", "3.98%", "2.71%", "1.81%", "0.46%", "3.33%", "…
$ `2021`  <chr> "4.89%", "...", "3.75%", "2.63%", "1.39%", "0.55%", "3.05%", "…
$ `2022`  <chr> "4.06%", "4.35%", "3.81%", "2.57%", "1.41%", "0.63%", "2.52%",…
$ `2023`  <chr> "7.39%", "5.29%", "3.59%", "2.49%", "1.41%", "0.76%", "2.46%",…
$ `2024`  <chr> "7.97%", "...", "3.52%", "2.51%", "1.00%", "0.72%", "2.83%", "…
head(df)
# A tibble: 6 × 77
  Country `1949` `1950` `1951` `1952` `1953` `1954` `1955` `1956` `1957` `1958`
  <chr>   <chr>  <chr>  <chr>  <chr>  <chr>  <chr>  <chr>  <chr>  <chr>  <chr> 
1 Algeria ...    ...    ...    ...    ...    ...    ...    ...    ...    ...   
2 Libya   ...    ...    ...    ...    ...    ...    ...    ...    ...    ...   
3 Morocco ...    ...    ...    ...    ...    ...    ...    1.18%  1.76%  2.14% 
4 Tunisia ...    ...    ...    ...    ...    ...    ...    ...    ...    ...   
5 Angola  ...    ...    ...    ...    ...    ...    ...    ...    ...    ...   
6 Benin   ...    ...    ...    ...    ...    ...    ...    ...    ...    ...   
# ℹ 66 more variables: `1959` <chr>, `1960` <chr>, `1961` <chr>, `1962` <chr>,
#   `1963` <chr>, `1964` <chr>, `1965` <chr>, `1966` <chr>, `1967` <chr>,
#   `1968` <chr>, `1969` <chr>, `1970` <chr>, `1971` <chr>, `1972` <chr>,
#   `1973` <chr>, `1974` <chr>, `1975` <chr>, `1976` <chr>, `1977` <chr>,
#   `1978` <chr>, `1979` <chr>, `1980` <chr>, `1981` <chr>, `1982` <chr>,
#   `1983` <chr>, `1984` <chr>, `1985` <chr>, `1986` <chr>, `1987` <chr>,
#   `1988` <chr>, `1989` <chr>, `1990` <chr>, `1991` <chr>, `1992` <chr>, …

Identify issues for cleaning/wrangling

I’ll need to get rid of the blank and ‘Note’ column, as well as the top five rows of background information, and delete the seventh row since it is blank. I’ll also covert the data type of percentage points from “Character” to “Numeric.” I’ll then delete rows such as ‘Americas’ or ‘Oceania’ that serve as region dividers, and add a column “region” to denote which region each country belongs to, add a column “year” to denote which year the data belongs to, and a column “spending” for the data.

Perform cleaning step by step

#add "region" column
Africa <- c(
  "Algeria",
  "Libya",
  "Morocco",
  "Tunisia",
  "Angola",
  "Benin",
  "Botswana",
  "Burkina Faso",
  "Burundi",
  "Cameroon",
  "Cape Verde",
  "Central African Republic",
  "Chad",
  "Congo, DR",
  "Congo, Republic",
  "Cote d'Ivoire",
  "Djibouti",
  "Equatorial Guinea",
  "Eritrea",
  "Ethiopia",
  "Gabon",
  "Gambia, The",
  "Ghana",
  "Guinea",
  "Guinea-Bissau",
  "Kenya",
  "Lesotho",
  "Liberia",
  "Madagascar",
  "Malawi",
  "Mali",
  "Mauritania",
  "Mauritius",
  "Mozambique",
  "Namibia",
  "Niger",
  "Nigeria",
  "Rwanda",
  "Senegal",
  "Seychelles",
  "Sierra Leone",
  "Somalia",
  "South Africa",
  "South Sudan",
  "Sudan",
  "Eswatini",
  "Tanzania",
  "Togo",
  "Uganda",
  "Zambia",
  "Zimbabwe"
)
Americas <- c(
  "Belize",
  "Costa Rica",
  "Cuba",
  "Dominican Republic",
  "El Salvador",
  "Guatemala",
  "Haiti",
  "Honduras",
  "Jamaica",
  "Mexico",
  "Nicaragua",
  "Panama",
  "Trinidad and Tobago",
  "Canada",
  "United States of America",
  "Argentina",
  "Bolivia",
  "Brazil",
  "Chile",
  "Colombia",
  "Ecuador",
  "Guyana",
  "Paraguay",
  "Peru",
  "Uruguay",
  "Venezuela"
)
Oceania <- c(
  "Australia",
  "Fiji",
  "New Zealand",
  "Papua New Guinea"
)
Asia <- c(
  "Afghanistan",
  "Bangladesh",
  "India",
  "Nepal",
  "Pakistan",
  "Sri Lanka",
  "China",
  "Japan",
  "Korea, North",
  "Korea, South",
  "Mongolia",
  "Taiwan",
  "Brunei",
  "Cambodia",
  "Indonesia",
  "Laos",
  "Malaysia",
  "Myanmar",
  "Philippines",
  "Singapore",
  "Thailand",
  "Timor Leste",
  "Viet Nam",
  "Kazakhstan",
  "Kyrgyz Republic",
  "Tajikistan",
  "Turkmenistan",
  "Uzbekistan"
)
Europe <- c(
  "Albania",
  "Bosnia and Herzegovina",
  "Bulgaria",
  "Croatia",
  "Czechia",
  "Czechoslovakia",
  "Estonia",
  "German Democratic Republic",
  "Hungary",
  "Kosovo",
  "Latvia",
  "Lithuania",
  "North Macedonia",
  "Montenegro",
  "Poland",
  "Romania",
  "Serbia",
  "Slovakia",
  "Slovenia",
  "Yugoslavia",
  "Armenia",
  "Azerbaijan",
  "Belarus",
  "Georgia",
  "Moldova",
  "Russia",
  "Ukraine",
  "USSR",
  "Austria",
  "Belgium",
  "Cyprus",
  "Denmark",
  "Finland",
  "France",
  "Germany",
  "Greece",
  "Iceland",
  "Ireland",
  "Italy",
  "Luxembourg",
  "Malta",
  "Netherlands",
  "Norway",
  "Portugal",
  "Spain",
  "Sweden",
  "Switzerland",
  "United Kingdom",
  "European Union"
)
Middle_East <- c(
  "Bahrain",
  "Egypt",
  "Iran",
  "Iraq",
  "Israel",
  "Jordan",
  "Kuwait",
  "Lebanon",
  "Oman",
  "Qatar",
  "Saudi Arabia",
  "Syria",
  "Türkiye",
  "United Arab Emirates",
  "Yemen, North",
  "Yemen"
)
df1 <- df %>%
  mutate(
    Region = case_when(
      Country %in% Africa       ~ "Africa",
      Country %in% Americas     ~ "Americas",
      Country %in% Asia         ~ "Asia",
      Country %in% Europe       ~ "Europe",
      Country %in% Middle_East  ~ "Middle East",
      Country %in% Oceania      ~ "Oceania",
      TRUE ~ "Other"
    )
  )
df2 <- df1 %>%
  pivot_longer(
    cols = `1949`:`2024`,
    names_to = "Year",
    values_to = "Value",
    values_drop_na = FALSE  # Keep NA values if desired
  ) %>%
  mutate(
    Value = as.numeric(gsub("%", "", Value)) / 100  # Assuming values are percentages, convert to decimal
  )
Warning: There was 1 warning in `mutate()`.
ℹ In argument: `Value = as.numeric(gsub("%", "", Value))/100`.
Caused by warning:
! NAs introduced by coercion
df3 <- df2 |>
  mutate(year = as.numeric(Year)) |>
  select(-Year)
colnames(df3)[colnames(df3) == "year"] <- "Year"
df_clean <- df3[, c("Country", "Region", "Year", "Value")] 
names(df_clean) <- c("country", "region", "year", "spending")
head(df_clean)
# A tibble: 6 × 4
  country region  year spending
  <chr>   <chr>  <dbl>    <dbl>
1 Algeria Africa  1949       NA
2 Algeria Africa  1950       NA
3 Algeria Africa  1951       NA
4 Algeria Africa  1952       NA
5 Algeria Africa  1953       NA
6 Algeria Africa  1954       NA

Save the cleaned version as .RData

save(df_clean, file = "military_clean.RData")